package book;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import common.DBManager;

public class WriteServet extends HttpServlet {
	
	Statement smt 		= null;
	Connection con 		= null;
	ResultSet rs 		= null;

	
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

//　入力パラメータの文字エンコーディング
		request.setCharacterEncoding("UTF-8");
		response.setContentType("text/html;charset=UTF-8");
		PrintWriter out = response.getWriter();
				
//　入力パラメータの取得
		String title 			= (String)request.getParameter("title");
		String genre 			= (String)request.getParameter("genre");
		String publisher 		= (String)request.getParameter("publisher");
		String writer 			= (String)request.getParameter("writer");
		String content 			= (String)request.getParameter("content");
		String publication_date = (String)request.getParameter("publication_date");
		String isbn 			= (String)request.getParameter("isbn");
		String picture			= (String)request.getParameter("picture");
			
		String path = "/book/userBookSerchResult.jsp";
		
		
		con = DBManager.getConnection();
		
		//セッション情報の、担当者IDと担当者名を読み込む
		HttpSession session = request.getSession();
		session.getAttribute("user_id");
		session.getAttribute("username");
		
		try {
			//	問い合わせ（SQL）を実行する準備
			smt  = con.createStatement();
			
			String manager_name =  (String) session.getAttribute("username");
			
			String  isql = "insert into  books(ID,TITLE,WRITER,GENRE_ID,PUBLISHER_ID,PUBLICATION_DATE,ISBN,CONTENT,PICTURE,DELETE_FLAG,OPEN_FLAG,RENTAL_ID,NEWEST_ID) " +
										"value(null,?,?,?,?,?,?,?,?,0,0,null,?)";
			
			PreparedStatement ist = con.prepareStatement(isql);

			ist.setString(1, title);
			ist.setString(2, writer);
			ist.setInt(3, Genre_id(genre));
			ist.setInt(4, Publisher_id(publisher));
			ist.setString(5, publication_date);
			ist.setString(6, isbn);
			ist.setString(7, content);
			ist.setString(8, picture);
			ist.setInt(9, Update_ID(manager_name));

			ist.executeUpdate();

			if(smt != null){
				try{smt.close();}
				catch(Exception e){}
			if(con != null){
				try{con.close();}
				catch(Exception e){}
			}
			}

		}catch (SQLException e){
			throw new ServletException(e);
		}finally{}


//　書籍検索結果画面へ遷移		
		RequestDispatcher dispatcher = request.getRequestDispatcher(path);
		dispatcher.include(request, response);
			}
	
	public int Update_ID(String manager_name) throws SQLException{
		
		//　updateテーブルに最終更新日時と更新者を作成して、その最終更新IDを取得
		String sql_newest = "insert into updates(NEWEST_ID,UPDATE_DATE,MANAGER_ID) " +
							"value(null,?,?)";
		PreparedStatement ist_newest = con.prepareStatement(sql_newest);
					
		ist_newest.setString(1,Zikoku());
		ist_newest.setString(2,manager_name);
					
		rs = smt.executeQuery(sql_newest);
					
		int udate_id = rs.getInt("NEWEST_ID");
		
		smt.close();
		con.close();
					
		return udate_id;
		
	}
	
	//　時間の取得
	public String Zikoku(){
		      Calendar calendar = Calendar.getInstance(); //インスタンス化
		         
		        int year = calendar.get(Calendar.YEAR);
			    int month = calendar.get(Calendar.MONTH) + 1;
			    int day = calendar.get(Calendar.DATE);
			    int hour =calendar.get(Calendar.HOUR_OF_DAY);
			    int minute = calendar.get(Calendar.MINUTE);
			    int second = calendar.get(Calendar.SECOND);
			    
			    String time = (year + "-" + month + "-" + day + "  " + hour + ":" + minute + ":" + second);
			    
			    return time;

		   }
	//　ジャンルIDを取得する
	public int Genre_id(String genre) throws SQLException{
		int genre_id 		= 0;
		con = DBManager.getConnection();
		smt = con.createStatement();
		
		// 書籍情報取得SQL文
		String sql = "select * from genres  where GENRE_NAME = '" + genre + "'";
		
		ResultSet rs = smt.executeQuery(sql);
		
		genre_id = rs.getInt("ID");
		
		
		smt.close();
		con.close();
		
		return genre_id;
	}
	
	//　出版者IDを取得する
	public int Publisher_id(String publisher) throws SQLException{
		
		int publisher_id 		= 9999999;
		con = DBManager.getConnection();
		smt = con.createStatement();
		
		// 書籍情報取得SQL文
		String sql = "select * from publishers  where GENRE_NAME = '" + publisher + "'";
		String sqlCnt = "select count(*) as cnt from publishers where GENRE_NAME = '" + publisher + "'";
		
		ResultSet rs = smt.executeQuery(sql);
		
		if(rs.next()){
			int coung = rs.getInt("cnt");
			if(coung == 0){
				String sql_max = "select max(ID) from publishers";
				rs = smt.executeQuery(sql_max);
				publisher_id = rs.getInt("ID") + 1;
				
				String sql_new_publisher = "insert into publishers(ID,PUBLISHER_NAME) " +
						"value(null,?,?)";
				PreparedStatement ist_newest = con.prepareStatement(sql_new_publisher);
				
				ist_newest.setInt(1,publisher_id);
				ist_newest.setString(2,publisher);
				ist_newest.executeUpdate();

			}else{
				
				publisher_id = rs.getInt("ID");
			}
			smt.close();
			con.close();
		}
		return publisher_id;
}
}
